# Copyright (c) 2024, lanlian and contributors
# For license information, please see license.txt

import frappe
from frappe import _
from frappe.utils.data import get_year_start, get_year_ending

def execute(filters=None):
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	tree_type = filters.get('tree_type')
	columns = [
		{ "label": _("1月"), "fieldtype": "Data", "fieldname": "m1", "width": 80, },
		{ "label": _("2月"), "fieldtype": "Data", "fieldname": "m2", "width": 80, },
		{ "label": _("3月"), "fieldtype": "Data", "fieldname": "m3", "width": 80, },
		{ "label": _("4月"), "fieldtype": "Data", "fieldname": "m4", "width": 80, },
		{ "label": _("5月"), "fieldtype": "Data", "fieldname": "m5", "width": 80, },
		{ "label": _("6月"), "fieldtype": "Data", "fieldname": "m6", "width": 80, },
		{ "label": _("7月"), "fieldtype": "Data", "fieldname": "m7", "width": 80, },
		{ "label": _("8月"), "fieldtype": "Data", "fieldname": "m8", "width": 80, },
		{ "label": _("9月"), "fieldtype": "Data", "fieldname": "m9", "width": 80, },
		{ "label": _("10月"), "fieldtype": "Data", "fieldname": "m10", "width": 80, },
		{ "label": _("11月"), "fieldtype": "Data", "fieldname": "m11", "width": 80, },
		{ "label": _("12月"), "fieldtype": "Data", "fieldname": "m12", "width": 80, },
		# { "label": _("Total"), "fieldtype": "Data", "fieldname": "row_total", "width": 80, },
	]
	if tree_type == 'EMS Equipment Card':
		add_column0 = { "label": _('Equipment Number'), "fieldtype": "Link", "fieldname": "tree_type", "width": 150, "options": "EMS Equipment Card",}
		add_column1 = { "label": _('Equipment Name'), "fieldtype": "Data", "fieldname": "equipment_name", "width": 100,}
		add_column2 = { "label": _('Equipment Model'), "fieldtype": "Data", "fieldname": "equipment_model", "width": 100,}
		columns.insert(0, add_column0)
		columns.insert(1, add_column1)
		columns.insert(2, add_column2)
	if tree_type == 'EMS Equipment Category':
		add_column0 = { "label": _('EMS Equipment Category'), "fieldtype": "Data", "fieldname": "tree_type", "width": 150,}
		columns.insert(0, add_column0)
	if tree_type == 'User Department':
		add_column0 = { "label": _('User Department'), "fieldtype": "Data", "fieldname": "tree_type", "width": 150,}
		columns.insert(0, add_column0)
	if tree_type == 'EMS Fault Effect':
		add_column0 = { "label": _('EMS Fault Effect'), "fieldtype": "Data", "fieldname": "tree_type", "width": 150,}
		columns.insert(0, add_column0)
	return columns



def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	tree_type, from_date, company = filters.get('tree_type'), filters.get('from_date'), filters.get('company')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	year_start = get_year_start(from_date)
	year_ending = get_year_ending(from_date)
	conditions += f" AND tb.happen_date >= '{year_start}' AND tb.happen_date <= '{year_ending}' "
	dt = [{'tree_type': 'haha', 'm1': 1, 'm2': 2, 'm3': 0, 'm4': 0, 'm5': 0, 'm6': 0, 'm7': 0, 'm8': 0, 'm9': 0, 'm10': 0, 'm11': 0, 'm12': 0, 'row_total': 3}]
	if tree_type == 'EMS Equipment Card':
		sql_card = f"""
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, IFNULL(count(tb.equipment_number), 0) as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 1 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, IFNULL(count(tb.equipment_number), 0) as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 2 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, IFNULL(count(tb.equipment_number), 0) as m3,  0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 3 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, IFNULL(count(tb.equipment_number), 0) as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 4 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, IFNULL(count(tb.equipment_number), 0)as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 5 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, IFNULL(count(tb.equipment_number), 0) as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 6 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, IFNULL(count(tb.equipment_number), 0) as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 7 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, IFNULL(count(tb.equipment_number), 0) as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 8 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, IFNULL(count(tb.equipment_number), 0) as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 9 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, IFNULL(count(tb.equipment_number), 0) as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 10 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, IFNULL(count(tb.equipment_number), 0) as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 11 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
			union all
			select tb.equipment_number as tree_type, tb.equipment_name, tb.equipment_model, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, IFNULL(count(tb.equipment_number), 0) as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 12 {conditions}
			group by tb.equipment_number, tb.equipment_name, tb.equipment_model
		"""
		dt = frappe.db.sql(sql_card, as_dict=True)
	if tree_type == 'EMS Equipment Category':
		sql_equipment_category = f"""
			select tb.equipment_category as tree_type, IFNULL(count(tb.equipment_number), 0) as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 1 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, IFNULL(count(tb.equipment_number), 0) as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 2 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, IFNULL(count(tb.equipment_number), 0) as m3,  0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 3 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, IFNULL(count(tb.equipment_number), 0) as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 4 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, IFNULL(count(tb.equipment_number), 0)as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 5 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, IFNULL(count(tb.equipment_number), 0) as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 6 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, IFNULL(count(tb.equipment_number), 0) as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 7 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, IFNULL(count(tb.equipment_number), 0) as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 8 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, IFNULL(count(tb.equipment_number), 0) as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 9 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, IFNULL(count(tb.equipment_number), 0) as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 10 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, IFNULL(count(tb.equipment_number), 0) as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 11 {conditions}
			group by tb.equipment_category
			union all
			select tb.equipment_category as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, IFNULL(count(tb.equipment_number), 0) as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 12 {conditions}
			group by tb.equipment_category
		"""
		dt = frappe.db.sql(sql_equipment_category, as_dict=True)	

	if tree_type == 'User Department':
		sql_user_department = f"""
			select tb.user_department as tree_type, IFNULL(count(tb.equipment_number), 0) as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 1 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, IFNULL(count(tb.equipment_number), 0) as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 2 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, IFNULL(count(tb.equipment_number), 0) as m3,  0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 3 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, IFNULL(count(tb.equipment_number), 0) as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 4 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, IFNULL(count(tb.equipment_number), 0)as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 5 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, IFNULL(count(tb.equipment_number), 0) as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 6 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, IFNULL(count(tb.equipment_number), 0) as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 7 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, IFNULL(count(tb.equipment_number), 0) as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 8 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, IFNULL(count(tb.equipment_number), 0) as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 9 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, IFNULL(count(tb.equipment_number), 0) as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 10 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, IFNULL(count(tb.equipment_number), 0) as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 11 {conditions}
			group by tb.user_department
			union all
			select tb.user_department as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, IFNULL(count(tb.equipment_number), 0) as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 12 {conditions}
			group by tb.user_department
		"""
		dt = frappe.db.sql(sql_user_department, as_dict=True)

	if tree_type == 'EMS Fault Effect':
		sql_fault_effect = f"""
			select tb.fault_effect as tree_type, IFNULL(count(tb.equipment_number), 0) as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 1 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, IFNULL(count(tb.equipment_number), 0) as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 2 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, IFNULL(count(tb.equipment_number), 0) as m3,  0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 3 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, IFNULL(count(tb.equipment_number), 0) as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 4 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, IFNULL(count(tb.equipment_number), 0)as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 5 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, IFNULL(count(tb.equipment_number), 0) as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 6 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, IFNULL(count(tb.equipment_number), 0) as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 7 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, IFNULL(count(tb.equipment_number), 0) as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 8 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, IFNULL(count(tb.equipment_number), 0) as m9, 0 as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 9 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, IFNULL(count(tb.equipment_number), 0) as m10, 0 as m11, 0 as m12
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 10 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, IFNULL(count(tb.equipment_number), 0) as m11, 0 as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 11 {conditions}
			group by tb.fault_effect
			union all
			select tb.fault_effect as tree_type, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, IFNULL(count(tb.equipment_number), 0) as m12 
			from `tabEMS Fault Report` tb 
			where tb.docstatus = 1 and tb.month = 12 {conditions}
			group by tb.fault_effect
		"""
		dt = frappe.db.sql(sql_fault_effect, as_dict=True)

	return dt
